﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Collections;
using System.Xml;
using Data.Model;
/*    
 * Common method that access datebase    
 * 
 * */
namespace Base.Common
{
    public class SQLHelper
    {
        //获取数据库连接字符串
        static string str = System.Configuration.ConfigurationManager.ConnectionStrings["BaseLine"].ConnectionString;
        static string DATA_TYPE = System.Configuration.ConfigurationManager.AppSettings["DATA_TYPE"];
        static string DATA_URL = System.Configuration.ConfigurationManager.AppSettings["DATA_URL"];

        //创建连接对象
        SqlConnection con = null;
        //创建命令对象
        SqlCommand cmd = null;
        //创建连接对象
        SqlDataAdapter da = null;
        DataSet ds = null;
        //创建一个表对象
        DataTable dt = null;

        #region 构造函数
        public SQLHelper()
        {
            try
            {
                con = new SqlConnection(str);
            }
            catch
            {

            }
        }
        #endregion

        #region 具体数据查询的方法
        /// <summary>
        /// 调用方法查询一个或多个表的信息，并返回DataSet类型值的方法
        /// </summary>
        /// <param name="sql">sql语句或存储过程名</param>
        /// <param name="re">是否是存储过程</param>
        /// <param name="values">参数</param>
        /// <returns>返回DataSet</returns>
        public DataSet ExecuteDataSet(string sql, bool re, params object[] values)
        {
            ds = new DataSet();
            da = new SqlDataAdapter(sql, con);

            if (re)
            {
                //定义命令类型
                da.SelectCommand.CommandType = CommandType.StoredProcedure;
            }//if (re)//判断命令类型

            if ((values != null) && (values.Length > 0))
            {
                //添加参数
                da.SelectCommand.Parameters.AddRange(values);
            }//if (values != null)//判断参数是否存在

            try
            {
                da.Fill(ds);

            }//try
            catch (SqlException ex)
            {

            }//try catch(SqlException ex)捕获数据库异常
            finally
            {

                if (con.State == ConnectionState.Open)
                {
                    //关闭连接
                    con.Close();
                }//if (con.State == ConnectionState.Open)关闭连接
            }//try catch(SqlException ex) finally

            return ds;

        }//public DataSet GetDataSet(string sql, bool re, params SqlParameter[] values)

        /// <summary>
        /// 调用方法查询单个表的信息，并返回DataTable类型值的方法
        /// </summary>
        /// <param name="sql">sql语句或存储过程名</param>
        /// <param name="re">是否是存储过程</param>
        /// <param name="values">参数</param>
        /// <returns>返回DataTable</returns>
        public DataTable ExecuteDataTable(string sql, bool re, params object[] values)
        {

            DataEntity de = new DataEntity();
            de.DataSource = str;
            de.DataType = DATA_TYPE;
            de.Key = sql;
            if (re)
            {
                de.CommandType = "StoredProcedure";
            }
            else
            {
                de.CommandType = "Text";
            }

            foreach (object item in values)
            {
                SqlParameter sqlparam = (SqlParameter)item;
                Parameter param = new Parameter();
                param.Key = sqlparam.ParameterName;
                param.Value = sqlparam.Value;
                param.Type = sqlparam.DbType;
                de.Param.Add(param);
            }

           string json =  Execute(ConvertHelper.ObjectToJson<DataEntity>(de));



            DataEntity deResult = ConvertHelper.JsonToObject<DataEntity>(json);
            dt = ConvertHelper.JsonToObject<DataTable>(deResult.Result.Value);

            return dt;
            //da = new SqlDataAdapter(sql, con);

            //if (re)
            //{
            //    //定义命令类型
            //    da.SelectCommand.CommandType = CommandType.StoredProcedure;
            //}//if (re)

            //if (values != null)
            //{
            //    //添加参数
            //    da.SelectCommand.Parameters.AddRange(values);
            //}//if (values != null)

            //try
            //{
            //    da.Fill(dt);
            //}//try
            //catch (SqlException ex)
            //{
            //}//try catch(SqlException ex)
            //finally
            //{

            //    if (con.State == ConnectionState.Open)
            //    {
            //        //关闭连接
            //        con.Close();
            //    }//if (con.State == ConnectionState.Open)

            //}//try catch(SqlException ex) finally

            //return dt;

        }//public DataTable GetDataTable(string sql, bool re, params SqlParameter[] values)

        /// <summary>
        /// 调用方法查询单个值，返回object类型值的方法
        /// </summary>
        /// <param name="sql">sql语句或存储过程名</param>
        /// <param name="re">是否是存储过程</param>
        /// <param name="values">参数</param>
        /// <returns>返回object</returns>
        public object ExecuteValue(string sql, bool re, params object[] values)
        {
            object strReturn = null;
            try
            {
                cmd = new SqlCommand(sql, con);
                if (re)
                {
                    //定义命令类型
                    cmd.CommandType = CommandType.StoredProcedure;
                }//if (re)

                if (values != null)
                {
                    //添加参数
                    cmd.Parameters.AddRange(values);
                }//if (values != null)


                if (con.State == ConnectionState.Closed)
                {
                    //打开连接
                    con.Open();
                }//if(con.State == ConnectionState.Closed)
                //读取一行一列的数据
                strReturn = cmd.ExecuteScalar();
                //如果返回值是数据库空值类型
                if (strReturn == DBNull.Value)
                {
                    strReturn = null;
                }
            }//try
            catch (SqlException ex)
            {
            }//try catch(SqlException ex)
            finally
            {

                if (con.State == ConnectionState.Open)
                {
                    //关闭连接
                    con.Close();
                }//if (con.State == ConnectionState.Open)

            }//try catch(SqlException ex) finally

            return strReturn;

        }//public object GetValue(string sql, bool re, params SqlParameter[] values)

        /// <summary>
        /// 调用方法更新数据库，并返回受影响行数
        /// </summary>
        /// <param name="sql">sql语句或存储过程名</param>
        /// <param name="re">是否是存储过程</param>
        /// <param name="values">参数</param>
        /// <returns>返回受影响行数</returns>
        public int ExecuteCommand(string sql, bool re, params object[] values)
        {
            int count = 0;
            cmd = new SqlCommand(sql, con);

            if (re)
            {
                //定义命令类型
                cmd.CommandType = CommandType.StoredProcedure;
            }//if(re)

            if (values != null)
            {
                //向命令对象的参数集合里添加参数
                cmd.Parameters.AddRange(values);
            }//if(values != null)

            try
            {

                if (con.State == ConnectionState.Closed)
                {
                    //打开连接
                    con.Open();
                }//if(con.State == ConnectionState.Closed)

                count = cmd.ExecuteNonQuery();//  执行命令，返回受影响行数
            }//try
            catch (SqlException ex)
            {
            }//try catch(SqlException ex)
            finally
            {

                if (con.State == ConnectionState.Open)
                {
                    //关闭连接
                    con.Close();
                }//if (con.State == ConnectionState.Open)

            }//try catch(SqlException ex) finally

            return count;

        }//public int ProExecuteCommand(string sql, bool re, params SqlParameter[] values)

        /// <summary>
        /// 获取参数的数组
        /// </summary>
        /// <param name="size"></param>
        /// <returns></returns>
        public object[] GetParameters(int size)
        {
            return new System.Data.SqlClient.SqlParameter[size];
        }

        /// <summary>
        /// 获取某个参数
        /// </summary>
        /// <returns></returns>
        public object GetParameter()
        {
            return new System.Data.SqlClient.SqlParameter();
        }
        public int ExecuteSql(string SQLString)
        {
            using (SqlConnection connection = new SqlConnection(str))
            {
                using (SqlCommand cmd = new SqlCommand(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        int rows = cmd.ExecuteNonQuery();
                        return rows;

                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        connection.Close();
                        throw e;

                    }
                }
            }
        }

        #region 分页查询
        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="sql">原始SQL语句</param>
        /// <param name="re">是否是存储过程</param>
        /// <param name="pageSize">每页显示的条数</param>
        /// <param name="pageIndex">当前页码</param>
        /// <param name="sort">排序字段名称</param>
        /// <param name="dir">升序/降序（asc/desc)</param>
        /// <param name="commandParameters">参数</param>
        /// <returns></returns>
        public DataSet ExecuteDataSet(string sql, bool re, int pageSize, int pageIndex, string sort, string dir, params object[] values)
        {
            if (!re)
            {
                string sqlCount = "SELECT COUNT(*) FROM (" + sql + ") TempTable;";
                string commandText = "  WITH Temp AS( SELECT * , ROW_NUMBER() OVER(ORDER BY " + sort + " " + dir + ") AS rowNo FROM"
                          + "(" + sql + ")a)SELECT * FROM Temp WHERE Temp .rowNo BETWEEN ((" + pageIndex + "-1)*" + pageSize + "+1) AND " + pageIndex + "*" + pageSize + "";
                return ExecuteDataSet(sqlCount + commandText, false, values);
            }
            else
            {
                return ExecuteDataSet(sql, true, values);
            }
        }
        #endregion

        #endregion


        /// <summary>
        ///Execute 
        /// </summary>
        /// <param name="json"></param>
        /// <returns></returns>
        private static string Execute(string json)
        {
            Hashtable pars = new Hashtable();
            pars["json"] = json;
            XmlDocument doc = WebSvcCaller.QuerySoapWebService(DATA_URL, "Execute", pars);
            return doc.SelectSingleNode("root").InnerText;
        }
    }
}

